package cnki.kg.demo.datatransfer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class DataWriter {
    public static void writeToMysql(Connection conn, String table, List<Map<String,Object>> data) throws SQLException {
        try {
            List<String> fields = new ArrayList<>(data.get(0).keySet());
            String sql=String.format("INSERT INTO %s (%s) values (%s)",table,fields.stream().map(n->String.format("`%s`",n)).collect(Collectors.joining(",")),appendParamFormat(fields.size()));
            if (conn != null) {
                // 设置事务为非自动提交
                conn.setAutoCommit(false);
                // 比起st，pst会更好些
                PreparedStatement pst = conn.prepareStatement(sql);//准备执行语句
                for (Map<String, Object> item : data) {
                    for (int index = 0; index < fields.size(); index++) {
                        pst.setObject(index+1, item.get(fields.get(index)));
                    }
                    // “积攒”sql语句
                    pst.addBatch();
                }
                // 执行操作
                pst.executeBatch();
                // 提交事务
                conn.commit();
                // 清空上一次添加的数据
                pst.clearBatch();
                pst.close();
               // conn.close();//外边关闭
            } else {
                System.out.println("获取连接失败");
            }
        } catch (SQLException e) {
            throw e;
        }
    }
    private static String appendParamFormat(int size) {
        String[] arr = new String[size];
        for (int i = 0; i < size; i++) {
            arr[i] = "?";
        }
        return String.join(",", arr);
    }
}
